package dao;

import bean.Cyclingrecord;
import util.Connect;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * The type Cyclingrecord dao.
 *
 * @author liu
 */
public class CyclingrecordDAO {

    /**
     * Gets page.
     *
     * @param userId   the user id
     * @param start    the start
     * @param pagesize the pagesize
     * @return the page
     */
    public List<Cyclingrecord> getPage(int userId, int start, int pagesize) {
        String sql = "SELECT * FROM cyclingrecord WHERE userId = ? LIMIT ?,? ";
        Connection connection = Connect.getInstance().getConnection();
        List<Cyclingrecord> cyclingrecordList = new ArrayList<>();

        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, userId);
            ps.setInt(2, start);
            ps.setInt(3, pagesize);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Cyclingrecord cyclingrecord = new Cyclingrecord();
                cyclingrecord.setCyclingrecordid(rs.getString("cyclingRecordId"));
                cyclingrecord.setBicycleid(rs.getInt("bicycleId"));
                cyclingrecord.setUserid(rs.getInt("userId"));
                cyclingrecord.setStarttime(rs.getTimestamp("startTime"));
                cyclingrecord.setEndtime(rs.getTimestamp("endTime"));
                cyclingrecord.setStartlocx(rs.getBigDecimal("startLocX"));
                cyclingrecord.setStartlocy(rs.getBigDecimal("startLocY"));
                cyclingrecord.setEndlocx(rs.getBigDecimal("endLocX"));
                cyclingrecord.setEndlocy(rs.getBigDecimal("endLocY"));
                cyclingrecordList.add(cyclingrecord);
            }

            Connect.getInstance().freeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return cyclingrecordList;
    }

    /**
     * Gets total.
     *
     * @return the total
     */
    public int getTotal() {
        String sql = "SELECT count(*) FROM cyclingrecord";
        Connection connection = Connect.getInstance().getConnection();
        int count = 0;
        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                count = rs.getInt(1);
            }

            Connect.getInstance().freeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * Find by c id optional.
     *
     * @param cid the cid
     * @return the optional
     * @throws SQLException the sql exception
     */
    public Optional<Cyclingrecord> findByCId(String cid) throws SQLException {
        String sql = "SELECT bicycleId, startLocX, startLocY, endLocX, endLocY FROM cyclingrecord WHERE cyclingRecordId = ?";
        Connection conn = Connect.getInstance().getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, cid);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            Cyclingrecord cr = new Cyclingrecord();
            cr.setBicycleid(rs.getInt("bicycleId"));
            cr.setStartlocx(rs.getBigDecimal("startLocX"));
            cr.setStartlocy(rs.getBigDecimal("startLocY"));
            cr.setEndlocx(rs.getBigDecimal("endLocX"));
            cr.setEndlocy(rs.getBigDecimal("endLocY"));
            return Optional.of(cr);
        } else {
            return Optional.empty();
        }
    }
}